-- Resets the SortOrder column on all records within a given Root Hierarchy so that
-- documents refered to are in alphanumeric order
CREATE PROCEDURE [dbo].[asi_HierarchyAlphaResort]
    @rootHierarchyKey uniqueidentifier
AS
BEGIN
   SET NOCOUNT ON

   CREATE TABLE #Reorder (
      NewSortOrder int IDENTITY (0,2048),
      HierarchyKey uniqueidentifier)

   EXEC asi_HierarchyAlphaResortChildren @rootHierarchyKey

   UPDATE h
      SET h.SortOrder = r.NewSortOrder
     FROM [dbo].[Hierarchy] h INNER JOIN #Reorder r ON h.HierarchyKey = r.HierarchyKey

   DROP TABLE #Reorder

   SET NOCOUNT OFF
END

GO